package com.df.springmvc.system.manage.dao;

import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import oracle.sql.DATE;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;

import com.common.bean.SQLResultBean;
import com.common.bean.SQLUpdateBean;
import com.df.springmvc.supper.SupperDao;
import com.google.gson.Gson;

/**
 * 本类只做数据库操作处理
 * @author David
 */
@Repository
public class IndexDao extends SupperDao{
	String getUser="";
	
	
	/**
	 * @param args
	 */
	public List<Object> getUserInfo(){
		Session session=getSessionFactory().getCurrentSession();
		SQLQuery query=session.createSQLQuery("select * from s_user where 1=1 or loginname=:u and password=:p");
		query.setString("u","admin");
		query.setString("p","MD5_232F297A57A5A743894A0E4A801FC3");
		query.setMaxResults(20);
		query.setFirstResult(1);
		return query.list();
	}
	public String getGetUser(){
		return getUser;
	}
	public void setGetUser(String getUser){
		this.getUser=getUser;
	}
	/**
	 * admin用户导航
	 * @param userId
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String,Object>> queryAdminMenuInfo(String userId) throws Exception{
		// TODO Auto-generated method stub
		String sql="select MODEL_PID,MODULE_ID,MODULE_NAME,MODULE_TYPE,URL,ICON  from  s_menu_info where STATUS='1' and systemStatus='1'  order by OrderNum ";
		ArrayList<HashMap<String, Object>> myAllList2 =  querySqlToSQLResultBean(sql).getMapList();
		
		HashMap<String, List<HashMap<String, Object>>> result = new HashMap<String, List<HashMap<String, Object>>>();
        List<HashMap<String, Object>> testList = new ArrayList<HashMap<String, Object>>();
    	for (int i = 0; i < myAllList2.size(); i++) {
    		testList.add(myAllList2.get(i));
		}
        for (HashMap<String, Object> okbean : testList) {
            List<HashMap<String, Object>> okbeans = result.get(okbean.get("MODEL_PID"));
            if (okbeans == null) {
                okbeans = new ArrayList<HashMap<String, Object>>();
                result.put((String) okbean.get("MODEL_PID"), okbeans);
            }
            okbeans.add(okbean);
        }
        ArrayList<HashMap<String,Object>> menuInfo = new ArrayList<HashMap<String,Object>>();
        displayMap(menuInfo,result, "0", "");
		return menuInfo;
	}
	/**
	 * 办公门户导航
	 * @param userId
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String,Object>> queryMenuInfo(String roleCode) throws Exception{
		// TODO Auto-generated method stub
//		String sql="select MODEL_PID,MODULE_ID,MODULE_NAME,MODULE_TYPE,URL,ICON  from  s_menu_info where STATUS='1' and systemStatus='1' order by OrderNum ";
//		ArrayList<HashMap<String, Object>> myAllList2 =  querySqlToSQLResultBean(sql).getMapList();
		ArrayList<HashMap<String, Object>> myAllList = new ArrayList<HashMap<String,Object>>();
		//获取一级菜单
		String sql1 = "select DISTINCT MODEL_PID,MODULE_ID,MODULE_NAME,MODULE_TYPE,URL,ICON,m.OrderNum   from  s_menu_info  m  INNER JOIN s_auth_info a on m.module_id= a.systemCode INNER JOIN sr_auth_role sar on sar.authCode = a.authCode  where m.STATUS='1'  and sar.roleCode in "+roleCode+" and m.MODEL_PID = '0'  order by m.OrderNum ";
		ArrayList<HashMap<String, Object>> myAllList1 =  querySqlToSQLResultBean(sql1).getMapList();
		if(myAllList1.size()==0){
			throw new Exception("当前登录用户角色没有配置系统功能使用权限，请联系系统管理员！");
		}
		for(int i=0;i < myAllList1.size();i++){
			 if("systemModular".equals(myAllList1.get(i).get("MODULE_ID"))){
				 myAllList1.remove(i);
		        }
        }
		myAllList.addAll(myAllList1);
		//获取二级菜单
		StringBuffer parameter2 =  new StringBuffer();//二级菜单的查询条件
		for(int i=0;i < myAllList1.size();i++){
			if(i==0){
				parameter2.append("('").append(myAllList1.get(i).get("MODULE_ID")+"").append("'");
			}else{
				parameter2.append(",'").append(myAllList1.get(i).get("MODULE_ID")+"").append("'");
			}
        }
		parameter2.append(")");
		String sql2 = "select DISTINCT MODEL_PID,MODULE_ID,MODULE_NAME,MODULE_TYPE,URL,ICON,m.OrderNum   from  s_menu_info  m  INNER JOIN s_auth_info a on m.module_id= a.modularCode INNER JOIN sr_auth_role sar on sar.authCode = a.authCode  where m.STATUS='1'  and sar.roleCode in "+roleCode+" and m.MODEL_PID in "+parameter2+"  order by m.OrderNum ";
		ArrayList<HashMap<String, Object>> myAllList2 =  querySqlToSQLResultBean(sql2,new Object[]{}).getMapList();
		myAllList.addAll(myAllList2);
		//获取三级菜单
		StringBuffer parameter3 =  new StringBuffer();//三级菜单的查询条件
		for(int i=0;i < myAllList1.size();i++){
			if(i==0){
				parameter3.append("('").append(myAllList2.get(i).get("MODULE_ID")+"").append("'");
			}else{
				parameter3.append(",'").append(myAllList2.get(i).get("MODULE_ID")+"").append("'");
			}
        }
		parameter3.append(")");
		String sql3 = "select DISTINCT MODEL_PID,MODULE_ID,MODULE_NAME,MODULE_TYPE,URL,ICON,m.OrderNum   from  s_menu_info  m  INNER JOIN s_auth_info a on m.module_id= a.functionCode INNER JOIN sr_auth_role sar on sar.authCode = a.authCode where m.STATUS='1' and sar.roleCode in "+roleCode+" and m.MODEL_PID in  "+parameter3+"  order by m.OrderNum ";
		ArrayList<HashMap<String, Object>> myAllList3 =  querySqlToSQLResultBean(sql3,new Object[]{}).getMapList();
		myAllList.addAll(myAllList3);
		
		HashMap<String, List<HashMap<String, Object>>> result = new HashMap<String, List<HashMap<String, Object>>>();
        List<HashMap<String, Object>> testList = new ArrayList<HashMap<String, Object>>();
    	for (int i = 0; i < myAllList.size(); i++) {
    		testList.add(myAllList.get(i));
		}
        for (HashMap<String, Object> okbean : testList) {
            List<HashMap<String, Object>> okbeans = result.get(okbean.get("MODEL_PID"));
            if (okbeans == null) {
                okbeans = new ArrayList<HashMap<String, Object>>();
                result.put((String) okbean.get("MODEL_PID"), okbeans);
            }
            okbeans.add(okbean);
        }
        ArrayList<HashMap<String,Object>> menuInfo = new ArrayList<HashMap<String,Object>>();
        displayMap(menuInfo,result, "0", "");
		return menuInfo;
	}
	public static void displayMap(ArrayList<HashMap<String,Object>> menuInfo,HashMap<String, List<HashMap<String, Object>>> result,String startKey, String str) {
		List<HashMap<String, Object>> list = result.get(startKey);
        for (HashMap<String, Object> okBean : list) {
            //System.out.println(str + okBean.get("MODULE_ID") + ":");
            menuInfo.add(okBean);
            if (result.containsKey(okBean.get("MODULE_ID"))) {
                displayMap(menuInfo,result, (String)okBean.get("MODULE_ID"), str + "  ");
            }
        }
    }
	/**
	 * 个人信息维护
	 */
	@Override
	public HashMap<String,Object> view(String loginName) throws Exception{
		// TODO Auto-generated method stub
		String sql="select o.*,(select orgName from s_org_info where status ='1' and orgCode =o.orgCode ) as orgName,(case  when o.incumbencyStatus='1' then '在职' else '离职' END) as incumbencyStatusName from b_staff_info as o  where status='1' and o.incumbencyStatus='1' and  o.userAccount=:userAccount  or o.incumbencyStatus='1' and  o.staffNumCode = :staffNumCode ";
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("userAccount",loginName);
		tmp.put("staffNumCode",loginName);
		ArrayList<HashMap<String,Object>> list=querySqlToSQLResultBean(sql,tmp,0,0).getMapList();
		if(list.size() == 1){
			return list.get(0);
		}else{
			throw new Exception("找不到数据");
		}
	}
	public ArrayList<HashMap<String, Object>> getMessageList(String loginName) throws Exception {
		// TODO Auto-generated method stub
		String sql = "SELECT  count(*) as total FROM b_message_info WHERE STATUS='1' AND sendee='"+loginName+"' AND isRead='0'  OR sendee=(SELECT userAccount FROM b_staff_info WHERE STATUS='1' AND incumbencyStatus='1' AND staffNumCode='"+loginName+"')";
		return querySqlToSQLResultBean(sql).getMapList();
	}
	/**
	 * 获取消息列表
	 * @param map
	 * @param attribute
	 * @param i
	 * @param j
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public ArrayList<Object> getMessageList(HashMap<String,String> map,Object[] attribute,int i,int j) throws Exception{
		// TODO Auto-generated method stub
		ArrayList<Object> allList = new ArrayList<Object>();
		Session session=getSessionFactory().getCurrentSession();
		String sql="SELECT  '' as ck,guid,messageCode,COMMENT,FromFor,receiveTime,(CASE WHEN isRead = '0' THEN '未读' ELSE '已读' END) as isRead FROM b_message_info WHERE STATUS='1' AND isRead='0' AND sendee='"+map.get("userId")+"' OR sendee=(SELECT userAccount FROM b_staff_info WHERE STATUS='1' AND incumbencyStatus='1' AND staffNumCode='"+map.get("userId")+"') order by receiveTime desc";
		SQLQuery query=session.createSQLQuery(sql);
		query.setFirstResult(i);
		query.setMaxResults(j);
		ArrayList<Object> list = (ArrayList<Object>) query.list();
		for (int k = 0; k < list.size(); k++) {
			allList.add(list.get(k));
		}
		Session session2=getSessionFactory().getCurrentSession();
		String sql2="SELECT  '' as ck,guid,messageCode,COMMENT,FromFor,receiveTime,(CASE WHEN isRead = '0' THEN '未读' ELSE '已读' END) as isRead FROM b_message_info WHERE STATUS='1' AND isRead='1' AND sendee='"+map.get("userId")+"' OR sendee=(SELECT userAccount FROM b_staff_info WHERE STATUS='1' AND incumbencyStatus='1' AND staffNumCode='"+map.get("userId")+"') order by receiveTime desc";
		SQLQuery query2=session2.createSQLQuery(sql2);
		query2.setFirstResult(i);
		query2.setMaxResults(j);
		ArrayList<Object> list2 = (ArrayList<Object>) query2.list();
		for (int m = 0; m < list2.size(); m++) {
			allList.add(list2.get(m));
		}
		return allList;
	}
	@Override
	public Object upd(HashMap<String,Object> map) throws Exception{
		// TODO Auto-generated method stub
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("guid",map.get("guid"));
		Object rs=excuteSqlBean(new SQLUpdateBean("b_message_info",map,tmp){});
		return rs;
	}
	/**
	 * 修改消息状态
	 * @param paramMap
	 * @return
	 * @throws Exception
	 */
	public Object updMessage(HashMap<String, Object> paramMap) throws Exception {
		// TODO Auto-generated method stub
		String myDate = (String) paramMap.get("myDate");
		int num = 1;
		Gson gson = new Gson(); 
		Map fromJson = gson.fromJson(myDate,Map.class);
		for (int i = 0; i < fromJson.size(); i++) {
			String guid="";
			ArrayList<String> list = (ArrayList<String>) fromJson.get(""+i+"");
			for (int j = 0; j < list.size(); j++) {
				guid = list.get(j);
				HashMap<String,Object> updmap=new HashMap<String,Object>(paramMap);
				updmap.remove("myDate");
				updmap.put("guid", guid);
				Object o=upd(updmap);
				if("1".equals(o)){
					continue;
				}else{
					throw new RuntimeException("修改失败");
				}
				
			}
		}
		return num;
	}
	/**
	 * 密码修改
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public Object updPassword(HashMap<String, Object> map) throws Exception {
		// TODO Auto-generated method stub
		Statement statement = dataSource2.getConnection().createStatement();
		String sql = "UPDATE S_USER_INFO  set  PASSWORD =  '"+ map.get("PASSWORD") +"' ,UPDATEBY = '"+ getCurrentUserBean().getUserId()+"',UPDATETIME = sysdate  WHERE STATUS = '1' AND  USERACCOUNT = '" + map.get("USERACCOUNT") +"'";
		int rs = statement.executeUpdate(sql);
		statement.close();
		return rs;
	}
	
}

